.TH MCLIENT 1 "NOVEMBER 2012" MonetDB "MonetDB Applications"
.SH NAME
mclient \- the MonetDB command-line tool
.SH SYNOPSIS
.B mclient
[
.I options
] [
.I file or database
[
.I file
\&... ] ]
.br
.B mclient
.B \-\-help
.SH DESCRIPTION
MonetDB is a database management system that is developed from a
main-memory perspective with use of a fully decomposed storage model,
automatic index management, extensibility of data types and search
accelerators, SQL- and JAQL- frontends.
.PP
.I Mclient
is the command-line interface to the MonetDB server.
.PP
If the
.BI \-\-statement= query
.RB ( \-s
.IR query )
option is given, the query is executed.
If any files are listed after the options, queries are read from the
files and executed.
The special filename
.B \-
refers to standard input.
Note that if there is both a
.B \-\-statement
option and filename arguments, the query given with
.B \-\-statement
is executed first.
If no
.B \-\-statement
option is given and no files are specified on the command line,
.I mclient
reads queries from standard input.
.PP
When reading from standard input, if standard input is a terminal
or if the
.B \-\-interactive
.RB ( \-i )
option is given,
.I mclient
interprets lines starting with
.B \e
(backslash) specially.
See the section BACKSLASH COMMANDS below.
.PP
Before
.I mclient
starts parsing command line options, it reads a
.I .monetdb
file.
If the environment variable
.B DOTMONETDBFILE
is set, it reads the file pointed to by that variable instead.
When unset,
.I mclient
searches for a
.I .monetdb
file in the current working directory, and if that doesn't exist, in the
current user's home directory.
This file can contain defaults for the flags
.BR user ,
.BR password ,
.BR language ,
.BR save_history ,
.BR format ,
and
.BR width .
For example, an entry in a
.I .monetdb
file that sets the default language for
.I mclient
to mal looks like this:
.BR language=mal .
To disable reading the
.I .monetdb
file, set the variable
.B DOTMONETDBFILE
to the empty string in the environment.
.SH OPTIONS
.SS
General Options
.TP
\fB\-\-help\fP (\fB\-?\fP)
Print usage information and exit.
.TP
\fB\-\-version\fP (\fB\-v\fP)
Print version information and exit.
.TP
\fB\-\-encoding=\fP\fIencoding\fP (\fB\-E\fP \fIencoding\fP)
Specify the character encoding of the input.
The option applies to both the standard input of
.I mclient
and to the argument of the
.B \-\-statement
.RB ( \-s )
option but not to the contents of files specified on the command line
(except for
.B \-
which refers to standard input) or files specified using the
.B \e<
command (those must be encoded using UTF-8).
The default encoding is taken from the locale.
.TP
\fB\-\-language=\fP\fIlanguage\fP (\fB\-l\fP \fIlanguage\fP)
Specify the query language.
The following languages are recognized:
.B mal
and
.BR sql .
A unique prefix suffices.
When the
.B \-\-language
option is omitted, the default of
.B sql
is assumed.
.TP
\fB\-\-database=\fP\fIdatabase\fP (\fB\-d\fP \fIdatabase\fP)
Specify the name or URI of the database to connect to.
The \fB-d\fP can be omitted if an equally named file does not exist in
the current directory.
As such, the first non-option argument will be interpreted as database
to connect to if the argument does not exist as file.
Valid URIs are as returned by `monetdb discover`, see
.IR monetdb (1),
and look like
\fBmapi:monetdb://\fP\fIhostname\fP\fB:\fP\fIport\fP\fB/\fP\fIdatabase\fP.
.TP
\fB\-\-host=\fP\fIhostname\fP (\fB\-h\fP \fIhostname\fP)
Specify the name of the host on which the server runs (default:
.BR localhost ).
When the argument starts with a forward slash (/), host is assumed to
be the directory where the UNIX sockets are stored for platforms where
these are supported.
.TP
\fB\-\-port=\fP\fIportnr\fP (\fB\-p\fP \fIportnr\fP)
Specify the portnumber of the server (default:
.BR 50000 ).
.TP
\fB\-\-interactive\fP[\fB=\fP\fItimermode\fP] (\fB\-i\fP[\fItimermode\fP])
When reading from standard input, interpret lines starting with
.B \e
(backslash) specially.
See the section BACKSLASH COMMANDS below.
This is the default if standard input is a terminal.
The optional \fItimermode\fP argument controls the
format of the time reported for queries.
Note that no space is allowed between
.B \-i
and
.IR timermode .
The default mode is
\fBhuman\fP which adjusts the time precision to the measured value.
The modes \fBms\fP, \fBs\fP and \fBm\fP force millisecond, second and
minute + second precision respectively.
.TP
\fB\-\-user\fP\fB=\fP\fIuser\fP (\fB\-u\fP \fIuser\fP)
Specify the user to connect as.
If this flag is absent, the client will ask for a user name, unless a
default was found in .monetdb file.
.TP
\fB\-\-format=\fP\fIformat\fP (\fB\-f\fP \fIformat\fP)
Specify the output format.
The possible values are
.BR sql ,
.BR csv ,
.BR tab ,
.BR raw ,
and
.BR xml .
.B csv
is comma-separated values,
.B tab
is tab-separated values,
.B raw
is no special formatting (data is dumped the way the server sends it
to the client),
.B sql
is a pretty format which is meant for human consumption, and
.B xml
is a valid (in the XML sense) document.
In addition to plain \fBcsv\fP, two other forms are possible.
\fBcsv=\fP\fIc\fP uses \fIc\fP as column separator; \fBcsv+\fP\fIc\fP
uses \fIc\fP as column separator and produces a single header line in
addition to the data.
.TP
\fB\-\-echo\fP (\fB\-e\fP)
Echo the query.
Note that using this option slows down processing.
.TP
\fB\-\-history\fP (\fB\-H\fP)
Load and save the command line history (default off).
.TP
\fB\-\-log=\fP\fIlogfile\fP (\fB\-L\fP \fIlogfile\fP)
Save client/server interaction in the specified file.
.TP
\fB\-\-statement=\fP\fIstmt\fP (\fB\-s\fP \fIstmt\fP)
Execute the specified query.
The query is run before any queries from files specified on the
command line are run.
.TP
\fB\-\-timezone\fP (\fB\-z\fP)
Do not tell the client's timezone to the server.
.TP
\fB\-\-Xdebug\fP (\fB\-X\fP)
Trace network interaction between
.I mclient
and the server.
.TP
\fB\-\-pager=\fP\fIcmd\fP (\fB\-|\fP \fIcmd\fP)
Send query output through the specified
.IR cmd .
One
.I cmd
is started for each query.
Note that the
.B |
will have to be quoted or else the shell will interpret it.
.SS
SQL Options
.TP
\fB\-\-null=\fP\fInullstr\fP (\fB\-n\fP \fInullstr\fP)
Set the string to be used as NULL representation when using the
sql, csv, or tab output formats.
If not used, NULL values are represented by the string \(dqnull\(dq in
the sql output format, and as the empty string in the csv and tab
output formats.
Note that an argument is required, so in order to use the empty
string, use \fB\-n \(dq\(dq\fP (with the space) or \fB\-\-null=\fP.
.TP
\fB\-\-autocommit\fP (\fB\-a\fP)
Switch autocommit mode off.
By default, autocommit mode is on.
.TP
\fB\-\-rows=\fP\fInr\fP (\fB\-r\fP \fInr\fP)
If specified, query results will be paged by an internal pager at the
specified number of lines.
.TP
\fB\-\-width=\fP\fInr\fP (\fB\-w\fP \fInr\fP)
Specify the width of the screen.
The default is the (initial) width of the terminal.
.TP
\fB\-\-dump\fP (\fB\-D\fP)
Create an SQL dump.
.TP
\fB\-\-inserts\fP (\fB\-N\fP)
Use INSERT INTO statements instead of COPY INTO + CSV values when
dumping the data of a table.
This option can be used when trying to load data from MonetDB into
another database, or when e.g. JDBC applications are used to reload
the dump.
.SH BACKSLASH COMMANDS
.SS
General Commands
.TP
\fB\e?\fP
Show a help message explaining the backslash commands.
.TP
\fB\eq\fP
Exit
.IR mclient .
.TP
\fB\e<\fP \fIfile\fP
Read input from the named
.IR file .
.TP
\fB\e>\fP \fIfile\fP
Write output to the named
.IR file .
If no
.I file
is specified, write to standard output.
.TP
\fB\e|\fP \fIcommand\fP
Pipe output to the given
.IR command .
Each query is piped to a new invocation of the
.IR command .
If no
.I command
is given, revert to writing output to standard output.
.TP
\fB\eh\fP
Show the
.IR readline (3)
history.
.TP
\fB\eL\fP \fIfile\fP
Log client/server interaction in the given
.IR file .
If no
.I file
is specified, stop logging information.
.TP
\fB\eX\fP
Trace what
.I mclient
is doing.
This is mostly for debugging purposes.
.TP
\fB\ee\fP
Echo the query in SQL formatting mode.
.TP
\fB\ef\fP \fIformat\fP
Use the specified
.I format
mode to format the output.
Possible modes the same as for the
.B \-\-format
.RB ( \-f )
option.
.TP
\fB\ew\fP \fIwidth\fP
Set the maximum page width for rendering in the
.B sql
formatting mode.
If
.I width
is
.BR \-1 ,
the page width is unlimited, when
.I width
is
.BR 0 ,
use the terminal width.
If
.I width
is greater than
.BR 0 ,
use the given width.
.TP
\fB\er\fP \fIrows\fP
Use an internal pager using
.I rows
per page.
If
.I rows
is
.BR \-1 ,
stop using the internal pager.
.SS
SQL Commands
.TP
\fB\eD\fP
Dump the complete database.
This is equivalent to using the program
.IR msqldump (1).
.TP
\fB\eD\fP \fItable\fP
Dump the given
.IR table .
.TP
\fB\ed\fP
Alias for \edvt.
.TP
\fB\ed[Stvsfn]+\fP
List database objects of the given type.
Multiple type specifiers can be used at the same time.
The specifiers \fIS\fP, \fIt\fP, \fIv\fP, \fIs\fP, \fIf\fP and \fIn\fP
stand for System, table, view, sequence, function and schema
respectively.
Note that \fIS\fP simply switches on viewing system catalog objects,
which is orthogonal on the other specifiers.
.TP
\fB\ed[Stvsfn]+\fP \fIobject\fP
Describe the given
.I object
in the database using SQL statements that reconstruct the object.
The same specifiers as above can be used, following the same rules.
When no specifiers are given,
.B vt
is assumed.
The object can be given with or without a schema, separated by a dot.
The object name can contain the wildcard characters
.B *
and
.B _
that represent zero or more, and exactly one character respectively.
An object name is converted to lowercase, unless the object name is
quoted by double quotes
.RB ( \(dq ).
Examples of this, are e.g.
.IR *.mytable ,
.IR tabletype* ,
or
.IR \(dqmyschema.FOO\(dq .
Note that wildcard characters do not work in quoted objects.
Quoting follows SQL quoting rules.
Arbitrary parts can be quoted, and two quotes following each other in
a quoted string represent the quote itself.
.TP
\fB\eA\fP
Enable auto commit mode.
.TP
\fB\ea\fP
Disable auto commit mode.
.SH EXAMPLES
Efficiently import data from a CSV (comma-separated values) file into
a table.
The file must be readable by the server.
.I $file
is the
absolute path name of the file,
.I $table
is the name of the table,
.I $db
is the name of the database.
.PP
mclient -d $db -s \(dqCOPY INTO $table FROM '$file' USING DELIMITERS ',','\e\en','\e\(dq'\(dq
.PP
Efficiently import data from a CSV file into a table when the file is
to be read by mclient (e.g. the server has no access to the file).
.I $file
is the (absolute or relative) path name of the file,
.I $table
is the name of the table,
.I $db
is the name of the database.
.PP
mclient -d $db -s \(dqCOPY INTO $table FROM STDIN USING DELIMITERS ',','\e\en','\e\(dq'\(dq - < $file
.PP
Note that in this latter case, if a count of records is supplied, it
should be at least as large as the number of records actually present
in the CSV file.
This, because otherwise the remainder of the file will be interpreted
as SQL queries.
.PP
See http://www.monetdb.org/Documentation/Manuals/SQLreference/CopyInto
for more information about the COPY INTO query.
.SH SEE ALSO
.IR msqldump (1),
.IR mserver5 (1)

